Connect and ReturnsRecords Properties Example (Client/Server)
This example uses the Connect and ReturnsRecords properties to select the top five book titles from a Microsoft SQL Server database based on year-to-date sales amounts. In the event of an exact match in sales amounts, the example increases the size of the list displaying the results of the query and prints a message explaining why this occurred.
Sub ClientServerX1()
Dim dbsCurrent As Database
Dim qdfPassThrough As QueryDef
Dim qdfLocal As QueryDef
Dim rstTopFive As Recordset
Dim strMessage As String
' Open a database from which QueryDef objects can be
' created.
Set dbsCurrent = OpenDatabase("DB1.mdb")
' Create a pass-through query to retrieve data from
' a Microsoft SQL Server database.
Set qdfPassThrough = _
dbsCurrent.CreateQueryDef("AllTitles")
' Note: The DSN referenced below must be set to
' use Microsoft Windows NT Authentication Mode to
' authorize user access to the Microsoft SQL Server.
qdfPassThrough.Connect = _
"ODBC;DATABASE=pubs;DSN=Publishers"
qdfPassThrough.SQL = "SELECT * FROM titles " & _
"ORDER BY ytd_sales DESC"
qdfPassThrough.ReturnsRecords = True
' Create a temporary QueryDef object to retrieve
' data from the pass-through query.
Set qdfLocal = dbsCurrent.CreateQueryDef("")
qdfLocal.SQL = "SELECT TOP 5 title FROM AllTitles"
Set rstTopFive = qdfLocal.OpenRecordset()
' Display results of queries.
With rstTopFive
strMessage = _
"Our top 5 best-selling books are:" & vbCr
Do While Not .EOF
strMessage = strMessage & " " & !Title & _
vbCr
.MoveNext
Loop
If .RecordCount > 5 Then
strMessage = strMessage & _
"(There was a tie, resulting in " & _
vbCr & .RecordCount & _
" books in the list.)"
End If
MsgBox strMessage
.Close
End With
' Delete new pass-through query because this is a
' demonstration.
dbsCurrent.QueryDefs.Delete "AllTitles"
dbsCurrent.Close
End Sub